'use strict';

const BaseService = require('./base');

class AppointmentService extends BaseService {
  get _model() {
    return 'Appointment';
  }

  async findOne({ id, simple = false }) {
    const { app } = this;
    const where = { ...this.formatWhere({ exact: { id } }) };
    let include = [];
    if (!simple) {
      include = [
        {
          model: app.model.User,
          attributes: {
            exclude: app.exclude.concat([ 'password' ]),
          },
          include: [
            {
              model: app.model.UserInfo,
              attributes: {
                exclude: app.exclude,
              },
            },
          ],
        },
        {
          model: app.model.House,
          attributes: {
            exclude: app.exclude,
          },
          include: [
            {
              model: app.model.Area,
              attributes: [ 'name' ],
            },
            {
              model: app.model.HouseInfo,
              attributes: {
                exclude: app.exclude,
              },
            },
            {
              model: app.model.User,
              attributes: {
                exclude: app.exclude.concat([ 'password' ]),
              },
              as: 'Owner',
              include: [
                {
                  model: app.model.UserInfo,
                  attributes: {
                    exclude: app.exclude,
                  },
                },
              ],
            },
          ],
        },
      ];
    }
    return this._findOne({ where, include });
  }

  async create({ startTime, endTime, phoneNum, persons, comment, status, reason, uid, houseId }) {
    return this._create({
      startTime, endTime, phoneNum, persons, comment, status, reason, uid, houseId,
    });
  }

  async update({ status, reason, id }) {
    const where = { id };
    const body = { status, reason };
    return this._update({ body, where });
  }

  async updateOther({ startTime, endTime, status, editStatus, reason, houseId }) {
    const where = this.rangeWhere({ startTime, endTime, status, houseId });
    const body = { status: editStatus, reason };
    return this._update({ body, where });
  }

  async destory(id) {
    const where = { id };
    return this._destory({ where });
  }

  async findList({ status, uid, oid, houseId, prop, sort, pageIndex, pageSize, createdAt }) {
    const { app } = this;
    const where = this.formatWhere({ exact: { status, uid, houseId }, range: { createdAt } });
    const whereHouse = this.formatWhere({ exact: { oid } });
    const order = this.formatOrder(prop, sort);
    const include = [
      {
        model: app.model.House,
        where: whereHouse,
        attributes: [ 'name', 'address' ],
        include: [
          {
            model: app.model.Area,
            attributes: [ 'name' ],
          },
          {
            model: app.model.HouseInfo,
            attributes: [ 'pictures' ],
          },
        ],
      },
      {
        model: app.model.User,
        attributes: {
          exclude: app.exclude,
        },
      },
    ];
    return this._findList({ pageIndex, pageSize, where, order, include });
  }

  async statisticalAppointment({ uid }) {
    const sql = 'SELECT `house`.`name`,COUNT(*) FROM `appointment` LEFT JOIN `house` ON `appointment`.`houseId` = `house`.id WHERE `house`.`oid` = ? GROUP BY `house`.`name` ORDER BY COUNT(*) LIMIT 5';
    return await this.querySelect(sql, [ uid ]);
  }

  async statisticalAppointmentAll() {
    const sql = 'SELECT `house`.`name`,COUNT(*) FROM `appointment` LEFT JOIN `house` ON `appointment`.`houseId` = `house`.id GROUP BY `house`.`name` ORDER BY COUNT(*) LIMIT 5';
    return await this.querySelect(sql);
  }

  async statisticalAppointmentAdd({ startTime, areaId }) {
    if (!startTime) {
      startTime = new Date().getFullYear();
    }
    let where = 'YEAR(`appointment`.`createdAt`) = ?';
    if (areaId) {
      where = 'YEAR(`appointment`.`createdAt`) = ? AND FIND_IN_SET(`house`.`areaId`, ?)';
    }
    const sql = `SELECT
          SUM(CASE WHEN MONTH(\`appointment\`.\`createdAt\`) = 1 THEN 1 ELSE 0 END) AS "1",
          SUM(CASE WHEN MONTH(\`appointment\`.\`createdAt\`) = 2 THEN 1 ELSE 0 END) AS "2",
          SUM(CASE WHEN MONTH(\`appointment\`.\`createdAt\`) = 3 THEN 1 ELSE 0 END) AS "3",
          SUM(CASE WHEN MONTH(\`appointment\`.\`createdAt\`) = 4 THEN 1 ELSE 0 END) AS "4",
          SUM(CASE WHEN MONTH(\`appointment\`.\`createdAt\`) = 5 THEN 1 ELSE 0 END) AS "5",
          SUM(CASE WHEN MONTH(\`appointment\`.\`createdAt\`) = 6 THEN 1 ELSE 0 END) AS "6",
          SUM(CASE WHEN MONTH(\`appointment\`.\`createdAt\`) = 7 THEN 1 ELSE 0 END) AS "7",
          SUM(CASE WHEN MONTH(\`appointment\`.\`createdAt\`) = 8 THEN 1 ELSE 0 END) AS "8",
          SUM(CASE WHEN MONTH(\`appointment\`.\`createdAt\`) = 9 THEN 1 ELSE 0 END) AS "9",
          SUM(CASE WHEN MONTH(\`appointment\`.\`createdAt\`) = 10 THEN 1 ELSE 0 END) AS "10",
          SUM(CASE WHEN MONTH(\`appointment\`.\`createdAt\`) = 11 THEN 1 ELSE 0 END) AS "11",
          SUM(CASE WHEN MONTH(\`appointment\`.\`createdAt\`) = 12 THEN 1 ELSE 0 END) AS "12"
      FROM (SELECT \`appointment\`.\`createdAt\` FROM \`appointment\` LEFT JOIN \`house\` ON \`appointment\`.\`houseId\` = \`house\`.\`id\` WHERE ${where}) AS \`appointment\``;
    return await this.querySelect(sql, [ startTime, areaId ]);
  }
}

module.exports = AppointmentService;
